﻿using System;
using System.Collections.Generic;
using System.Text;
using Peekaboo2008.model;
using System.Data;
using System.Data.SqlClient;
using Peekaboo2008.util;

namespace Peekaboo2008.dao
{
    class PhieuNhapXuatDAO
    {
        public static DataTable GetAll()
        {
            return DataUtil.executeStore("sp_PhieuNhapXuat_GetAll", null);
        }

        public static PhieuNhapXuatModel GetById(String _ID)
        {
            PhieuNhapXuatModel _PhieuNhap = null;
            int i = 0;
            SqlParameter[] param = new SqlParameter[1];
            param[i] = new SqlParameter("ID", SqlDbType.Int);
            param[i++].Value = _ID;
            DataTable _Db =  DataUtil.executeStore("sp_PhieuNhapXuat_GetById", param);
            if (_Db != null)
            {
                foreach(DataRow _Row in _Db.Rows ){
                _PhieuNhap = new PhieuNhapXuatModel();
                _PhieuNhap.ID = _Row["ID"].ToString();
                _PhieuNhap.KyHieu = _Row["Ki_hieu"].ToString();
                _PhieuNhap.LoaiNhapXuat = _Row["Loai_nhap_xuat"].ToString();
                _PhieuNhap.MaHinhThucThanhToan = _Row["Ma_hinh_thuc_thanh_toan"].ToString();
                _PhieuNhap.MaKhachHangNhaCungCap = _Row["Ma_khach_hang"].ToString();
                _PhieuNhap.MaNgoaiTe = _Row["Ma_ngoai_te"].ToString();
                _PhieuNhap.MaNhanVien = _Row["Ma_nhan_vien"].ToString();
                _PhieuNhap.NgapCapNhapCuoi = _Row["Ngay_cap_nhat_cuoi"].ToString();
                _PhieuNhap.ThanhTien = _Row["Thanh_tien"].ToString();
                _PhieuNhap.ThanhToan = _Row["Thanh_toan"].ToString();
                _PhieuNhap.Thue = _Row["Thue"].ToString();
                _PhieuNhap.TienChietKhau = _Row["Tien_chiet_khau"].ToString();
                _PhieuNhap.TienNo = _Row["Tien_no"].ToString();
                _PhieuNhap.TienThue = _Row["Tien_thue"].ToString();
                _PhieuNhap.TongTien = _Row["Tong_tien"].ToString();
                _PhieuNhap.TyGia = _Row["Ty_gia"].ToString();
                _PhieuNhap.XuatHoaDon = _Row["Xuat_hoa_don"].ToString();
                _PhieuNhap.GhiChu = _Row["Ghi_chu"].ToString();
                _PhieuNhap.ChietKhau = _Row["Chiet_khau"].ToString();

                }
            }
            return _PhieuNhap;
        }
        public static DataTable GetByFromTo(String MaChiNhanh,String Opts,String IgnoreDateTime, String From, String To)
        {
            int i = 0;
            SqlParameter[] param = new SqlParameter[5];
            param[i] = new SqlParameter("MaChiNhanh", SqlDbType.Int);
            param[i++].Value = MaChiNhanh;
            param[i] = new SqlParameter("Opts", SqlDbType.Int);
            param[i++].Value = Opts;
            param[i] = new SqlParameter("IgnoreDateTime", SqlDbType.Int);
            param[i++].Value = IgnoreDateTime;
            param[i] = new SqlParameter("From", SqlDbType.VarChar);
            param[i++].Value = From;
            param[i] = new SqlParameter("To", SqlDbType.VarChar);
            param[i++].Value = To;

            return DataUtil.executeStore("sp_PhieuNhapXuat_GetByFromTo", param);
        }
        public static String GetMaHoaDonById(String Id)
        {
            int i = 0;
            SqlParameter[] param = new SqlParameter[1];
            param[i] = new SqlParameter("ID", SqlDbType.Int);
            param[i++].Value = Id;

            return DataUtil.executeStore("sp_PhieuNhapXuat_GetMaHoaDonById", param).Rows[0][0].ToString();
        }

        public static DataTable GetHoaDonXuatBanHangHoaByMaxId()
        {
            return DataUtil.executeStore("sp_PhieuXuatNhap_GetHoaDonXuatBanHangHoaByMaxId", null);
        }
        //*****************************************Using SQL Transaction*********************************************


        /**
         * Nhap hang tu nha cung cap,neu trong kho da co mat hang thi chi can update, con neu chua co the insert
         * 
         * 
         * */
        public static int Insert(PhieuNhapXuatModel _PhieuNhapXuatModel, List<ChiTietNhapXuatModel> _ListChiTietNhapXuatModels, String _MaChiNhanh)
        {
            int _ReturnID = -1;
            List<QueryModel> _ListQueries = new List<QueryModel>();
            QueryModel _PhieuNhapXuatQuery = null;
            QueryModel _ChiTieNhapXuatQuery = null;

            //Lap phieu hang
            int i = 0;
            SqlParameter[] _PhieuNhapXuatParam = new SqlParameter[16];
            _PhieuNhapXuatParam[i] = new SqlParameter("Ki_hieu", SqlDbType.NVarChar);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.KyHieu;
            _PhieuNhapXuatParam[i] = new SqlParameter("Ghi_chu", SqlDbType.NVarChar);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.GhiChu;
            _PhieuNhapXuatParam[i] = new SqlParameter("Ma_khach_hang", SqlDbType.NVarChar);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.MaKhachHangNhaCungCap;
            _PhieuNhapXuatParam[i] = new SqlParameter("Ma_nhan_vien", SqlDbType.Int);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.MaNhanVien;
            _PhieuNhapXuatParam[i] = new SqlParameter("Tong_tien", SqlDbType.Float);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.TongTien;
            _PhieuNhapXuatParam[i] = new SqlParameter("Thue", SqlDbType.Float);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.Thue;
            _PhieuNhapXuatParam[i] = new SqlParameter("Tien_thue", SqlDbType.Float);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.TienThue;
            _PhieuNhapXuatParam[i] = new SqlParameter("Thanh_tien", SqlDbType.Float);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.ThanhTien;
            _PhieuNhapXuatParam[i] = new SqlParameter("Thanh_toan", SqlDbType.Float);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.ThanhToan;
            _PhieuNhapXuatParam[i] = new SqlParameter("Chiet_khau", SqlDbType.Float);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.ChietKhau;
            _PhieuNhapXuatParam[i] = new SqlParameter("Tien_chiet_khau", SqlDbType.Float);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.TienChietKhau;
            _PhieuNhapXuatParam[i] = new SqlParameter("Ty_gia", SqlDbType.Float);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.TyGia;
            _PhieuNhapXuatParam[i] = new SqlParameter("Tien_no", SqlDbType.Float);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.TienNo;
            _PhieuNhapXuatParam[i] = new SqlParameter("Ma_hinh_thuc_thanh_toan", SqlDbType.Int);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.MaHinhThucThanhToan;
            _PhieuNhapXuatParam[i] = new SqlParameter("Ma_ngoai_te", SqlDbType.Int);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.MaNgoaiTe;
            _PhieuNhapXuatParam[i] = new SqlParameter("ReturnID", SqlDbType.Int);
            _PhieuNhapXuatParam[i].Direction = ParameterDirection.Output;

            _PhieuNhapXuatQuery = new QueryModel("sp_PhieuXuatNhap_InsertHoaDonNhapHangNCC", _PhieuNhapXuatParam);

            _ListQueries.Add(_PhieuNhapXuatQuery);

            SqlParameter[] _ChiTietNhapXuatParam = null;
            foreach (ChiTietNhapXuatModel _ChiTietNhapXuat in _ListChiTietNhapXuatModels)
            {
                //Nhap tung don vi chi tiet cua don hang
                int j = 0;
                _ChiTietNhapXuatParam = new SqlParameter[14];
                _ChiTietNhapXuatParam[j] = new SqlParameter("Ma_hang_hoa", SqlDbType.Int);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.MaHangHoa;
                _ChiTietNhapXuatParam[j] = new SqlParameter("Ghi_chu", SqlDbType.NVarChar);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.GhiChu;
                _ChiTietNhapXuatParam[j] = new SqlParameter("So_luong", SqlDbType.Float);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.SoLuong;
                _ChiTietNhapXuatParam[j] = new SqlParameter("Gia_ban", SqlDbType.Float);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.GiaBan;
                _ChiTietNhapXuatParam[j] = new SqlParameter("Thanh_tien", SqlDbType.Float);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.ThanhTien;
                _ChiTietNhapXuatParam[j] = new SqlParameter("Thue", SqlDbType.Float);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.Thue;
                _ChiTietNhapXuatParam[j] = new SqlParameter("Tien_thue", SqlDbType.Float);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.TienThue;
                _ChiTietNhapXuatParam[j] = new SqlParameter("Tong_tien", SqlDbType.Float);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.TongTien;
                _ChiTietNhapXuatParam[j] = new SqlParameter("Chiet_khau", SqlDbType.Float);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.ChietKhau;
                _ChiTietNhapXuatParam[j] = new SqlParameter("Tien_chiet_khau", SqlDbType.Float);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.TienChietKhau;
                _ChiTietNhapXuatParam[j] = new SqlParameter("Han_su_dung", SqlDbType.VarChar);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.HanSuDung;
                _ChiTietNhapXuatParam[j] = new SqlParameter("Bao_hanh", SqlDbType.VarChar);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.BaoHanh;
                _ChiTietNhapXuatParam[j] = new SqlParameter("Phi_van_chuyen", SqlDbType.Float);
                _ChiTietNhapXuatParam[j++].Value = _ChiTietNhapXuat.PhiVanChuyen;
                _ChiTietNhapXuatParam[j] = new SqlParameter("So_luong_goc", SqlDbType.Float);
                _ChiTietNhapXuatParam[j].Value = _ChiTietNhapXuat.SoLuongGoc;

                _ChiTieNhapXuatQuery = new QueryModel("sp_ChiTietNhapXuat_Insert", _ChiTietNhapXuatParam);
                _ListQueries.Add(_ChiTieNhapXuatQuery);
            }
            if (DataUtil.executeMultiNonStore(_ListQueries))
                _ReturnID = Int32.Parse(_PhieuNhapXuatParam[i].Value.ToString());
            return _ReturnID;
        }

        public static Boolean DeleteByID(String _ID)
        {
            int i = 0;
            SqlParameter[] _PhieuNhapXuatParam = new SqlParameter[1];
            _PhieuNhapXuatParam[i] = new SqlParameter("ID", SqlDbType.Int);
            _PhieuNhapXuatParam[i].Value = _ID;
            return DataUtil.executeNonStore("sp_PhieuXuatNhap_DeleteById", _PhieuNhapXuatParam);
        }


        public static Boolean NhapHangTuNhaCungCap_InHoaDon(PhieuNhapXuatModel _PhieuNhapXuatModel, List<ChiTietNhapXuatModel> _ListChiTietNhapXuatModels, String _MaChiNhanh)
        {
            List<QueryModel> _ListQueries = new List<QueryModel>();
            QueryModel _PhieuNhapXuatQuery = null;
            QueryModel _HangTonQuery = null;

            
            int i = 0;
            //Update trang thai in hoa don cua bang PhieuNhapXuat
            SqlParameter[] _PhieuNhapXuatParam = new SqlParameter[1];
            _PhieuNhapXuatParam[i] = new SqlParameter("ID", SqlDbType.Int);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.ID;

            _PhieuNhapXuatQuery = new QueryModel("sp_PhieuNhapXuat_UpdateInHoaDon", _PhieuNhapXuatParam);
            _ListQueries.Add(_PhieuNhapXuatQuery);

            //Cap nhap bang hang ton
            SqlParameter[] _HangTonParam = null;
            foreach (ChiTietNhapXuatModel _ChiTietNhapXuat in _ListChiTietNhapXuatModels)
            {

                i = 0;
                //Update so luong ben bang hang ton
                if (HangTonDAO.SearchByMaChiNhanh_Mahang(_MaChiNhanh, _ChiTietNhapXuat.MaHangHoa) != -1)
                {
                    _HangTonParam = new SqlParameter[3];
                    _HangTonParam[i] = new SqlParameter("Ma_chi_nhanh", SqlDbType.Int);
                    _HangTonParam[i++].Value = _MaChiNhanh;
                    _HangTonParam[i] = new SqlParameter("Ma_hang_hoa", SqlDbType.Int);
                    _HangTonParam[i++].Value = _ChiTietNhapXuat.MaHangHoa;
                    _HangTonParam[i] = new SqlParameter("So_luong", SqlDbType.Float);
                    _HangTonParam[i].Value = _ChiTietNhapXuat.SoLuong;
                    _HangTonQuery = new QueryModel("sp_HangTon_UpdateSoLuongTonKhoOfNhapHangNCCByMaChiNhanh_MaHang", _HangTonParam);
                }
                else
                {
                    _HangTonParam = new SqlParameter[7];
                    _HangTonParam[i] = new SqlParameter("Ma_hang", SqlDbType.Int);
                    _HangTonParam[i++].Value = _ChiTietNhapXuat.MaHangHoa;
                    _HangTonParam[i] = new SqlParameter("So_luong", SqlDbType.Float);
                    _HangTonParam[i++].Value = _ChiTietNhapXuat.SoLuong;
                    _HangTonParam[i] = new SqlParameter("Ghi_chu", SqlDbType.NVarChar);
                    _HangTonParam[i++].Value = _ChiTietNhapXuat.GhiChu;
                    
                    _HangTonParam[i] = new SqlParameter("Gia_nhap_cuoi", SqlDbType.Float);
                    _HangTonParam[i++].Value = _ChiTietNhapXuat.GiaBan;
                    _HangTonParam[i] = new SqlParameter("Gia_xuat_cuoi", SqlDbType.Float);
                    _HangTonParam[i++].Value = _ChiTietNhapXuat.GiaBan;
                    _HangTonParam[i] = new SqlParameter("Ma_chi_nhanh", SqlDbType.Int);
                    _HangTonParam[i++].Value = _MaChiNhanh;
                    _HangTonParam[i] = new SqlParameter("OutID", SqlDbType.Int);
                    _HangTonParam[i].Direction = ParameterDirection.Output;

                    _HangTonQuery = new QueryModel("sp_HangTon_InsertTonKhoOfNhapHangByMaChiNhanh_MaHang", _HangTonParam);
                }
                _ListQueries.Add(_HangTonQuery);
            }
            return DataUtil.executeMultiNonStore(_ListQueries);
        }


        public static Boolean XuatBanHangHoa_InHoaDon(PhieuNhapXuatModel _PhieuNhapXuatModel, List<ChiTietNhapXuatModel> _ListChiTietNhapXuatModels, String _MaChiNhanh)
        {
            List<QueryModel> _ListQueries = new List<QueryModel>();
            QueryModel _PhieuNhapXuatQuery = null;
            QueryModel _HangTonQuery = null;


            int i = 0;
            //Update trang thai in hoa don cua bang PhieuNhapXuat
            SqlParameter[] _PhieuNhapXuatParam = new SqlParameter[1];
            _PhieuNhapXuatParam[i] = new SqlParameter("ID", SqlDbType.Int);
            _PhieuNhapXuatParam[i++].Value = _PhieuNhapXuatModel.ID;

            _PhieuNhapXuatQuery = new QueryModel("sp_PhieuNhapXuat_UpdateInHoaDon", _PhieuNhapXuatParam);
            _ListQueries.Add(_PhieuNhapXuatQuery);

            //Cap nhap bang hang ton
            SqlParameter[] _HangTonParam = null;
            foreach (ChiTietNhapXuatModel _ChiTietNhapXuat in _ListChiTietNhapXuatModels)
            {
                i = 0;
                _HangTonParam = new SqlParameter[3];
                _HangTonParam[i] = new SqlParameter("Ma_chi_nhanh", SqlDbType.Int);
                _HangTonParam[i++].Value = _MaChiNhanh;
                _HangTonParam[i] = new SqlParameter("Ma_hang_hoa", SqlDbType.Int);
                _HangTonParam[i++].Value = _ChiTietNhapXuat.MaHangHoa;
                _HangTonParam[i] = new SqlParameter("So_luong", SqlDbType.Float);
                _HangTonParam[i].Value = _ChiTietNhapXuat.SoLuong;
                _HangTonQuery = new QueryModel("sp_HangTon_UpdateSoLuongTonKhoOfBanHangByMaChiNhanh_MaHang", _HangTonParam);

                _ListQueries.Add(_HangTonQuery);
            }
            return DataUtil.executeMultiNonStore(_ListQueries);
        }




        //*****************************************End SQL Transaction*********************************************
    }
}
